package jdbc.statement;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;

/**
 * Created with IntelliJ IDEA
 * Description:学习PreparedStatement
 * User:22954
 * Date:2024-05-12
 * Time:14:30
 */
public class PreparedStatement_ {
    static Scanner scanner;

    public static void main(String[] args) throws IOException, SQLException {
        scanner = new Scanner(System.in);
        Properties properties = new Properties();
        properties.load(new FileInputStream("src/jdbc/mysql.properties"));
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        Connection connection = DriverManager.getConnection(url, user, password);
//        select(connection);
//        dml(connection,3);
        selectAll(connection);
        connection.close();
    }
    private static void selectAll(Connection connection) throws SQLException {
        String sql = "select * from `admin`";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();//获取到的resultSet指向第0行
        while(resultSet.next()){
            //String getString(String columnLabel)
            //String getString(int columnIndex)
            //getXXX()既可填字段名,也可填字段索引,从1开始
            String name = resultSet.getString("name");
//            String name = resultSet.getString(1);
            String password = resultSet.getString(2);
            System.out.println("user: " + name + "\tpassword: " + password);
        }
        resultSet.close();
        preparedStatement.close();
    }

    private static void select(Connection connection) throws SQLException, IOException {
        //sql语句中的 ? 表示占位符,索引从1开始
        String sql = "select * from `admin` where `name` = ? and `password` = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        System.out.print("请输入用户名:");
        String adminName = scanner.nextLine();
        System.out.print("请输入用户密码:");
        String adminPassword = scanner.nextLine();
        preparedStatement.setString(1, adminName);
        preparedStatement.setString(2, adminPassword);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            System.out.println("Success");
        } else {
            System.out.println("Failed");
        }
        resultSet.close();
        preparedStatement.close();
    }

    private static void dml(Connection connection, int choice) throws SQLException {
        PreparedStatement preparedStatement = switch (choice) {
            case 1 -> insert(connection);
            case 2 -> update(connection);
            case 3 -> delete(connection);
            default -> null;
        };
        int i = preparedStatement.executeUpdate();
        if (i > 0) {
            System.out.println("Success");
        } else {
            System.out.println("Failed");
        }
        preparedStatement.close();
    }

    private static PreparedStatement insert(Connection connection) throws SQLException {
        String sql = "insert into `admin` values(?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        System.out.print("请输入新用户名:");
        String adminName = scanner.nextLine();
        System.out.print("请输入新用户密码:");
        String adminPassword = scanner.nextLine();
        preparedStatement.setString(1, adminName);
        preparedStatement.setString(2, adminPassword);
        return preparedStatement;
    }

    private static PreparedStatement update(Connection connection) throws SQLException {
        String sql = "update `admin` set `password` = ? where `name` = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        System.out.print("请输入要更改的用户名:");
        String adminName = scanner.nextLine();
        System.out.print("请输入新用户密码:");
        String adminPassword = scanner.nextLine();
        preparedStatement.setString(2, adminName);
        preparedStatement.setString(1, adminPassword);
        return preparedStatement;
    }

    private static PreparedStatement delete(Connection connection) throws SQLException {
        String sql = "delete from `admin` where `name` = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        System.out.print("请输入要删除的用户名:");
        String adminName = scanner.nextLine();
        preparedStatement.setString(1, adminName);
        return preparedStatement;
    }
}
